package com.haohope.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.Map;
import java.util.regex.Pattern;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.BuiltinFormats;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.eventusermodel.XSSFReader.SheetIterator;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.xml.sax.Attributes;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;
import org.xml.sax.helpers.XMLReaderFactory;

import com.haohope.excel.utils.DateUtil;
import com.haohope.excel.utils.NumberUtil;

/**
 * 名称: ExcelXlsxReader.java<br>
 * 描述: <br>
 * 类型: JAVA<br>
 * 最近修改时间:2016年7月5日 上午10:00:52<br>
 * 
 * @since 2016年7月5日
 * @author “”
 */
public class ExcelXlsxReader extends DefaultHandler {
	/**
     * 共享字符串表
     */
    private SharedStringsTable sst;
    /**
     * 工作表索引
     */
    private int sheetIndex = -1;
    /**
     * 工作表名字
     */
    private String sheetName = "";
    /**
     * 单元格
     */
    private StylesTable stylesTable;
    private final DataFormatter formatter = new DataFormatter();
	private Map<String,Map<String,String>> attrMap = new HashMap<String,Map<String,String>>();
	private Map<String,String> rowObj = new HashMap<String,String>();
	private IExcelRowReader rowReader;
    public void setRowReader(IExcelRowReader rowReader) {
        this.rowReader = rowReader;
    }
    public void process(InputStream is)  throws IOException, OpenXML4JException, SAXException {
    	OPCPackage pkg = OPCPackage.open(is);
        XSSFReader xssfReader = new XSSFReader(pkg);
        stylesTable = xssfReader.getStylesTable();
        SharedStringsTable sst = xssfReader.getSharedStringsTable();
        XMLReader parser = this.fetchSheetParser(sst);
        //Iterator<InputStream> sheets = xssfReader.getSheetsData();
        //update by xiongx
        SheetIterator sheets = (SheetIterator)xssfReader.getSheetsData();
        while (sheets.hasNext()) {
            sheetIndex++;
            InputStream sheet = sheets.next();
            sheetName = sheets.getSheetName();
            InputSource sheetSource = new InputSource(sheet);
            parser.parse(sheetSource);
            sheet.close();
        }
    } 
    /**
     * 遍历工作簿中所有的电子表格
     */
    public void process(String filename) throws IOException, OpenXML4JException, SAXException {
    	process(new FileInputStream(new File(filename)));
//        OPCPackage pkg = OPCPackage.open(filename);
//        XSSFReader xssfReader = new XSSFReader(pkg);
//        stylesTable = xssfReader.getStylesTable();
//        SharedStringsTable sst = xssfReader.getSharedStringsTable();
//        XMLReader parser = this.fetchSheetParser(sst);
//        Iterator<InputStream> sheets = xssfReader.getSheetsData();
//        while (sheets.hasNext()) {
//            sheetIndex++;
//            InputStream sheet = sheets.next();
//            InputSource sheetSource = new InputSource(sheet);
//            parser.parse(sheetSource);
//            sheet.close();
//        }
    }
    public XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException {
        XMLReader parser = XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");
        this.sst = sst;
        parser.setContentHandler(this);
        return parser;
    }
    @Override
    public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException {
    	if("row".equals(name) || "c".equals(name) || "mergeCell".equals(name)){
    		//attrMap.put(name, attributes);
    		Map<String,String> attribute = new HashMap<String,String>();
    		if("row".equals(name)){//spans
    			//当前位置
    			attribute.put("r", attributes.getValue("r"));
    			//spans
    			attribute.put("spans", attributes.getValue("spans"));
    		}else if("c".equals(name)){
    			//位置
    			attribute.put("r", attributes.getValue("r"));
    			//类型
    			attribute.put("t", attributes.getValue("t"));
    			//样式
    			attribute.put("s", attributes.getValue("s"));
    		}else if("mergeCell".equals(name)){
    			//合并单元格列
    			attribute.put("ref", attributes.getValue("ref"));
    		}
    		attrMap.put(name, attribute);
    	}
    }
    //去空格以及换行
    private Pattern pattern = Pattern.compile("\\s*|\t|\r|\n");
    /**
     * <p>Description:获取单元格的值</p>
     * @param v 值
     * @param t 类型
     * @param s 样式
     * @return String
     * @Note
     * <p>Author: <a href="http://www.jianshu.com/users/1bda2d4550e2/latest_articles" target="_blank">LuoQ</a></p>
     * <p>Date: 2017年9月12日 下午8:32:12</p>
     * <p>Version: 1.0</p>
     */
    @SuppressWarnings("unused")
	private String getValue(String v,String t,String s){
    	//如果是日期 不要去空格
    	if(StringUtils.isNotEmpty(v) && !DateUtil.isValidDate(v)) {
    		v = DateUtil.filterIllegalDateStr(v);
    	}
    	int formatIndex = -1;
        String formatString = null;
        String realv = "";
        if ("b".equals(t)) {
        	char first = v.charAt(0);
        	realv=first=='0'?"FALSE":"TRUE";
        } else if ("e".equals(t)) {
        	realv = "ERROR:" + v.toString();
        } else if ("inlineStr".equals(t)) {
        	XSSFRichTextString rtsi = new XSSFRichTextString(v.toString());
        	realv = rtsi.toString();
            rtsi = null;
        } else if ("s".equals(t)) {
        	String sstIndex = v.toString();
            try {
                int idx = Integer.parseInt(sstIndex);
                XSSFRichTextString rtss = new XSSFRichTextString(sst.getEntryAt(idx));
                realv = rtss.toString();
                rtss = null;
            } catch (NumberFormatException ex) {
            	realv = v.toString();
            }
        } else if ("str".equals(t)) {
            //nextDataType = CellDataType.FORMULA;
        	realv = "";
        }else if (s != null) {
            XSSFCellStyle style = stylesTable.getStyleAt(Integer.parseInt(s));
            formatIndex = style.getDataFormat();
            formatString = style.getDataFormatString();
            //System.out.println("+++++++formatIndex:"+formatString);
            //System.out.println("+++++++formatString:"+formatString);
            if ("m/d/yy" == style.getDataFormatString()) {
            	formatString = "yyyy-MM-dd hh:mm:ss.SSS";
                realv = formatter.formatRawCellContents(Double.parseDouble(v),formatIndex,formatString);
                // 对日期字符串作特殊处理
                //realv = realv.replace(" ", "\"T\"");
            }else {
            	if (style.getDataFormatString() == null) {
                	realv = "";
                    formatString = BuiltinFormats.getBuiltinFormat(formatIndex);
                }else{
                	realv = v;
                }
            }
        }else{
        	if (formatString != null) {
        		realv = formatter.formatRawCellContents(Double.parseDouble(v), formatIndex, formatString).trim();
            } else {
            	realv = v;
            }
        	realv = realv.replace("_", "").trim();
        }
        if(StringUtils.isNotEmpty(realv)  && !DateUtil.isValidDate(v)) {
        	realv = realv.trim().replace(" ", "");
        	realv = pattern.matcher(realv).replaceAll("");
    	}
    	return realv;
    }
    @Override
    public void characters(char[] ch, int start, int length) throws SAXException {
    	// 得到单元格内容的值
    	String v = new String(ch, start, length);
    	Map<String,String> row = attrMap.get("row");
    	String r = row.get("r");
    	Map<String,String> c = attrMap.get("c");
    	String r_ = c.get("r");
    	String t_ = c.get("t");
    	String s_ = c.get("s");
    	v = getValue(v, t_, s_);

    	//update by xiongx  解决丢失数据问题
    	String key = r_.replace(r, "").toLowerCase();
    	String oldval = rowObj.get(key);
    	if(StringUtils.isNotEmpty(oldval)) {
    		if(StringUtils.isNotEmpty(v)) {
    			v = oldval + v;
    		}else {
    			v = oldval;
    		}
    	}
    	v = NumberUtil.commachar2num(v);
    	rowObj.put(key, v);
    	//rowObj.put(r_.replace(r, "").toLowerCase(), v);
    }
	@Override
    public void endElement(String uri, String localName, String name) throws SAXException {
    	if("row".equals(name) || "c".equals(name) || "mergeCell".equals(name)){
    		Map<String,String> row = attrMap.get(name);
    		if("row".equals(name)){
    			//rowReader.appendRow(sheetIndex+1,Integer.valueOf(row.get("r")),rowObj);
    			rowReader.appendRow(StringUtils.isEmpty(sheetName)?("sheet"+(sheetIndex+1)):sheetName,sheetIndex+1,Integer.valueOf(row.get("r")),rowObj);
    			rowObj.clear();
    		}else if("mergeCell".equals(name)){
    			rowReader.appendRef(row.get("ref"));
    		}
    		attrMap.remove(name);
    	}
    }
    /**
     * 计算两个单元格之间的单元格数目(同一行)
     * 
     * @param ref
     * @param preRef
     * @return
     */
    public int countNullCell(String ref, String preRef) {
        // excel2007最大行数是1048576，最大列数是16384，最后一列列名是XFD
        String xfd = ref.replaceAll("\\d+", "");
        String xfd_1 = preRef.replaceAll("\\d+", "");

        xfd = fillChar(xfd, 3, '@', true);
        xfd_1 = fillChar(xfd_1, 3, '@', true);

        char[] letter = xfd.toCharArray();
        char[] letter_1 = xfd_1.toCharArray();
        int res = (letter[0] - letter_1[0]) * 26 * 26 + (letter[1] - letter_1[1]) * 26 + (letter[2] - letter_1[2]);
        return res - 1;
    }

    /**
     * 字符串的填充
     * 
     * @param str
     * @param len
     * @param let
     * @param isPre
     * @return
     */
    String fillChar(String str, int len, char let, boolean isPre) {
        int len_1 = str.length();
        if (len_1 < len) {
            if (isPre) {
                for (int i = 0; i < (len - len_1); i++) {
                    str = let + str;
                }
            } else {
                for (int i = 0; i < (len - len_1); i++) {
                    str = str + let;
                }
            }
        }
        return str;
    }
	public String getSheetName() {
		return sheetName;
	}
	public void setSheetName(String sheetName) {
		this.sheetName = sheetName;
	}
}